using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Management.Smo;
/*
using Microsoft.SqlServer.Management.Smo.CoreEnum;
using Microsoft.SqlServer.Management.Smo.Wmi;
*/

using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Configuration;
using System.Reflection;
namespace DataBaseManagement
{
    public class Negocio
    {
        string loginProfessor;
        public Negocio(string loginProfessor)
        {
            this.loginProfessor = loginProfessor;
        }
        public bool GravarConfiguracoesUsuarioNoBanco(string login, string senha, string servidor, string bancoDeDados)
        {
            Server server = ObterServidor(servidor);

            Database db = server.Databases[bancoDeDados];
            if (db == null)
            {
                db = new Database(server, bancoDeDados);
                db.Create();
            }
            Schema sc = db.Schemas[login];
            if (sc == null)
            {
                sc = new Schema(db, login);
                sc.Create();
            }

            Login l = new Login(server, login);
            if (server.Logins.Contains(login))
                l = server.Logins[login];

            l.PasswordPolicyEnforced = false;
            l.PasswordExpirationEnabled = false;
            l.DefaultDatabase = db.Name;
            if (!server.Logins.Contains(login))
            {
                l.LoginType = LoginType.SqlLogin;
                l.Create(senha);
            }
            else
            {
                l.ChangePassword(senha);
                l.Alter();
            }
            List<string> roles = new List<string>();
            roles.AddRange(
                Properties.Settings.Default.DatabasePermission.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries)
            );

            foreach (string us in new string[] { login, loginProfessor })
            {
                User u = new User(db, us);
                
                //limpando as roles...
                foreach (string role in u.EnumRoles())
                {
                    bool achou = roles.Exists( 
                            new Predicate<string>( 
                                delegate( string s)
                                {
                                    return s.ToUpper() == role.ToUpper();
                                }));
                    
                    if (!achou)
                    {
                        db.Roles[role].DropMember(us);
                    }
                }

                if (!db.Users.Contains(us))
                {
                    u.Login = us;
                    u.UserType = UserType.SqlLogin;
                    u.Create();
                }
                //foreach (DatabasePermissionInfo info in db.EnumDatabasePermissions(us))
                //{
                //    db.Revoke(info.PermissionType, info.Grantee, false, true);
                //}

                DatabasePermissionSet permissionSet;
                permissionSet = ObterPermissoesDefault();
                //permissionSet = new DatabasePermissionSet(DatabasePermission.References);
                u = db.Users[us];
                var regras = Properties.Settings.Default.Roles.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string r in regras)
                {
                    if (!u.IsMember(r))
                    {
                        u.AddToRole(r);
                    }
                }
                
                //db.Deny(ObterDatabasePermissionSetForAdmin(), us);
                
                db.Grant(permissionSet, us, false);
                db.Deny(ObterDatabasePermissionToDeny(), us, false);
            }
            return true;
        }

        public static Server ObterServidor(string servidor)
        {

            String connectionString = "Data Source=" + servidor + ";Initial Catalog=master;Integrated Security=True";
            Server server = new Server(servidor);
            return server;
        }
        private DatabasePermissionSet ObterDatabasePermissionSetForAdmin()
        {
            DatabasePermissionSet permissao = new DatabasePermissionSet(); 
            var Tdbp = typeof(DatabasePermission);
            foreach (PropertyInfo propertyInfo in Tdbp.GetProperties(BindingFlags.Public | BindingFlags.Static | BindingFlags.GetProperty))
            {
                DatabasePermission databasePermissao = (DatabasePermission)propertyInfo.GetValue(null, null);
                permissao.Add(databasePermissao);
            }
            
            return permissao;
        }
        private DatabasePermissionSet ObterDatabasePermissionToDeny()
        {
            DatabasePermissionSet permissao = new DatabasePermissionSet();
            
            var roles = Properties.Settings.Default.DenyDatabasePermission.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

            var Tdbp = typeof(DatabasePermission);
            foreach (string r in roles)
            {
                PropertyInfo pinfo = Tdbp.GetProperty(r);
                DatabasePermission databasePermissao = (DatabasePermission)pinfo.GetValue(null, null);
                permissao.Add(databasePermissao);
            }
            return permissao;
        }
        private DatabasePermissionSet ObterPermissoesDefault()
        {

            DatabasePermissionSet permissao = new DatabasePermissionSet();
            
            var roles = Properties.Settings.Default.DatabasePermission.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

            
            var Tdbp = typeof(DatabasePermission);
            foreach (string r in roles)
            {
                PropertyInfo pinfo = Tdbp.GetProperty(r);
                DatabasePermission databasePermissao = (DatabasePermission)pinfo.GetValue(null, null);
                permissao.Add(databasePermissao);
            }
            /*
            //permissao.Add(DatabasePermission.Alter);
            
            permissao.Add(DatabasePermission.Alter);
            permissao.Add(DatabasePermission.Authenticate);
            permissao.Add(DatabasePermission.Connect);
            permissao.Add(DatabasePermission.CreateDefault);
            //permissao.Add(DatabasePermission.CreateFunction);
            //permissao.Add(DatabasePermission.CreateProcedure);
            permissao.Add(DatabasePermission.CreateTable);
            //permissao.Add(DatabasePermission.CreateView);
            permissao.Add(DatabasePermission.Delete);
            permissao.Add(DatabasePermission.Execute);
            permissao.Add(DatabasePermission.Insert);
            permissao.Add(DatabasePermission.Select);
            permissao.Add(DatabasePermission.Update);
            permissao.Add(DatabasePermission.ViewDefinition);
            
            permissao.Add(DatabasePermission.References);
            */ 
            return permissao;
        }

        public bool ExisteDataBase(string servidor, string nomeDoBanco)
        {
            Server serv = ObterServidor(servidor);
            return  serv.Databases.Contains(nomeDoBanco);
        }
        public Database ObterDataBase(string servidor, string nomeDoBanco)
        {
            if (ExisteDataBase(servidor, nomeDoBanco))
            {
                Server serv = ObterServidor(servidor);
                return serv.Databases[nomeDoBanco];
            }
            else
            {
                return null;
            }
        }


        public void ApagarBancoDoAluno(string servidor, string bancoDeDados, string login)
        {
            String connectionString = "Data Source=" + servidor + ";Initial Catalog=master;Integrated Security=True";
            Server server = new Server(servidor);

            Database db = server.Databases[bancoDeDados];
            if (db == null)
            {
                return;
            }
            db.Drop();

            if (!server.Logins.Contains(login))
                return;
            Login l = server.Logins[login];
            l.Drop();

            return;
        }

        public static string CalcularHashMD5(byte[] input_buffer)
        {
            // create implementation of MD5
            MD5 md5 = new MD5CryptoServiceProvider();
            // get hash
            byte[] md5Gerado = md5.ComputeHash(input_buffer);
            string HashMD5 = System.Convert.ToBase64String(md5Gerado);
            return BitConverter.ToString(md5Gerado).Replace("-","");
        }
    }
}
